package com.jadesoft.common.service;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.log4j.Logger;
import org.hibernate.Query;
import org.hibernate.transform.AliasToEntityMapResultTransformer;
import org.springframework.beans.BeanUtils;
import org.springframework.util.Assert;

import com.ufida.leaf.exception.DataAccessException;
import com.ufida.leaf.persistence.HibernateHelper;
import com.ufida.leaf.persistence.PersistenceImp;
import com.ufida.leaf.util.BeanHelper;

@SuppressWarnings("unchecked")
public class PaginationService extends PersistenceImp {
	protected final Logger logger = Logger.getLogger(getClass());
//	protected IPersistence persistence = Platform.getPersistence();

	public Page pagedQuery(String hql, int start, int limit, Object args[])
    {
        Assert.hasText(hql);

        String countQueryString = (new StringBuilder("SELECT count(*) ")).append(removeSelect(removeOrders(hql))).toString();
        Query countQuery = HibernateHelper.currentSession().createQuery(countQueryString);
        for(int i = 0; i < args.length; i++)
        	countQuery.setParameter(i, args[i]);
        Long totalCount = (Long) countQuery.uniqueResult();
        if(totalCount==null){
        	totalCount = 0L;
        }
        if(totalCount < 1L)
            return new Page();

        Query query = HibernateHelper.currentSession().createQuery(hql);
        for(int i = 0; i < args.length; i++)
        	query.setParameter(i, args[i]);
        List list = query.setFirstResult(start).setMaxResults(limit).list();
        
        return new Page(start, totalCount, limit, list);
    }
	
	public Page pagedSQLQuery(String sql, int start, int limit, Object[] args, Class clazz) {
		String countQueryString = (new StringBuilder("select count(*) ")).append(removeSelect(removeOrders(sql))).toString();
		Query countQuery = HibernateHelper.currentSession().createSQLQuery(countQueryString);
		for(int i = 0; i < args.length; i++)
        	countQuery.setParameter(i, args[i]);
		
		long totalCount = 0;
		if(countQuery.uniqueResult()==null){
			totalCount = 0;
		}else{
			totalCount = ((Number)countQuery.uniqueResult()).longValue();
		}
        if(totalCount < 1L)
            return new Page();
        Query query = HibernateHelper.currentSession().createSQLQuery(sql);
        for(int i = 0; i < args.length; i++)
        	query.setParameter(i, args[i]);
        List list = query.setResultTransformer(new AliasToBeanResultTransformer(clazz)).setFirstResult(start).setMaxResults(limit).list();
		return new Page(start, totalCount, limit, list);
	}
	
	public Page pagedSQLQueryForSubQuery(String sql, int start, int limit, Object[] args, Class clazz) {
		String countQueryString = (new StringBuilder("select count(*) from (")).append(sql).append(") ").toString();
		Query countQuery = HibernateHelper.currentSession().createSQLQuery(countQueryString);
		for(int i = 0; i < args.length; i++)
        	countQuery.setParameter(i, args[i]);
		
		long totalCount = 0;
		if(countQuery.uniqueResult()==null){
			totalCount = 0;
		}else{
			totalCount = ((Number)countQuery.uniqueResult()).longValue();
		}
        if(totalCount < 1L)
            return new Page();
        Query query = HibernateHelper.currentSession().createSQLQuery(sql);
        for(int i = 0; i < args.length; i++)
        	query.setParameter(i, args[i]);
        List list = query.setResultTransformer(new AliasToBeanResultTransformer(clazz)).setFirstResult(start).setMaxResults(limit).list();
		return new Page(start, totalCount, limit, list);
	}
	
	public List sqlQuery(String sql, Class clazz, Object...args) {
		Query query = HibernateHelper.currentSession().createSQLQuery(sql);
		if (args != null) {
			for(int i = 0; i < args.length; i++)
				query.setParameter(i, args[i]);
		}
		return query.setResultTransformer(new AliasToBeanResultTransformer(clazz)).list();
	}
	
	static public class AliasToBeanResultTransformer extends AliasToEntityMapResultTransformer {
		private static final long serialVersionUID = -5656068582158143577L;
		private Class clazz;
		public AliasToBeanResultTransformer(Class clazz) {
			this.clazz = clazz;
		}
		public Object transformTuple(Object[] tuple, String[] aliases) {
			Map result = new HashMap(tuple.length);
			for (int i = 0; i < tuple.length; i++) {
				String alias = aliases[i];
				if (alias != null)
					result.put(toBeanConvention(alias.toLowerCase()), tuple[i]);
			}
			if (clazz == null) {
				return result;
			}
			Object instance = BeanUtils.instantiateClass(clazz);
			BeanHelper.copyProperties(result, instance);
			return instance;
		}
	}

    private static String removeOrders(String hql)
    {
        Pattern p = Pattern.compile("ORDER\\s*by[\\w|\\W|\\s|\\S]*", 2);
        Matcher m = p.matcher(hql);
        StringBuffer sb = new StringBuffer();
        for(; m.find(); m.appendReplacement(sb, ""));
        m.appendTail(sb);
        return sb.toString();
    }

    private static String removeSelect(String hql)
    {
        Assert.hasText(hql);
        int beginPos = hql.toLowerCase().indexOf("from");
        Assert.isTrue(beginPos != -1, (new StringBuilder(" hql : ")).append(hql).append(" must has a keyword 'from'").toString());
        return hql.substring(beginPos);
    }
    
    private static String toBeanConvention(String ori) {
    	Pattern p = Pattern.compile("_([a-zA-Z])");
		Matcher m = p.matcher(ori);
		StringBuffer sb = new StringBuffer();
		while (m.find()) {
			m.appendReplacement(sb, m.group(1).toUpperCase());
		}
		m.appendTail(sb);
		return sb.toString();
    }
    
    /**
     * 以参数绑定的方式查询,返回查询的结果集
     * @param hql
     * @param params
     * @return
     */
    public List query(String hql ,Object[] params){
    	 Query query = HibernateHelper.currentSession().createQuery(hql);
         for(int i = 0; i < params.length; i++)
         	query.setParameter(i, params[i]);
    	return query.list();
    }
    
    /**
     * 根据KEY,删除所有记录
     * @param ids
     * @param className
     * @param key
     * @throws DataAccessException
     */
	public void removeObjs(Object[] keys, String className, String key)
			throws DataAccessException {
		String id = null;
		try {
			StringBuffer buffer = new StringBuffer(1024);
			String lowerClassName = className.toLowerCase();
			buffer.append("from ");
			buffer.append(className);
			buffer.append(" as ");
			buffer.append(lowerClassName);
			buffer.append(" where ");
			buffer.append(lowerClassName);
			buffer.append(".");
			buffer.append(key);
			buffer.append(" in (");
			for (int i = 0; i < keys.length; ++i) {
				id = keys[i].toString();
				buffer.append("'").append(id).append("',");
			}
			buffer.deleteCharAt(buffer.length() - 1);
			buffer.append(")");
			removeObjs(buffer.toString());
		} catch (Exception ex) {
			throw new DataAccessException(ex);
		}
	}
	
    /**
     * 获取第一条记录
     * @param hql
     * @param params
     * @return
     */
    public Object queryFirst(String hql ,Object[] params){
    	 Query query = HibernateHelper.currentSession().createQuery(hql);
         for(int i = 0; i < params.length; i++)
         	query.setParameter(i, params[i]);
         List list = query.setFirstResult(1).setMaxResults(1).list();
    	return list.size() == 0 ? null : list.get(0);
    }
    
    /**
     * 通过SQL查询结果集
     * @param sql
     * @param clazz 返回的数据封装的对象
     * @return
     */
	public List queryBySql(String sql){
		return HibernateHelper.currentSession().createSQLQuery(sql).list();
	}
}
